package com.example.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.dto.statistic;
import com.example.pojo.major;
import com.example.pojo.previous;
import com.example.pojo.tracking;
import org.apache.ibatis.annotations.*;

import java.util.List;


@Mapper
public interface TrackMapper {
    //更新用户就业信息 (用户端)
    @Update("update tracking set Status=#{Status},Employer=#{Employer},Position=#{Position},UnemployReason =#{UnemployReason},PhoneNumber=#{PhoneNumber},Name=#{Name} where StudentID=#{StudentID}")
    void updateTrackInfo(tracking t);

    //插入新用户就业信息 (用户端)
    @Insert("insert into tracking(StudentID,Employer,Position,Status,UnemployReason,PhoneNumber,Name) "+
            "values(#{StudentID},#{Employer},#{Position},#{Status},#{UnemployReason},#{PhoneNumber},#{Name})")
    void insertTrackInfo(tracking t);

    //根据学号查找信息 (用户端)
    @Select("select * from tracking where StudentID=#{id} ")
    tracking findRecordById(String id);

    //统计就业人数和未就业人数（管理员端）
    @Select("SELECT"+
            " SUM(CASE WHEN Status = 1 THEN 1 ELSE 0 END) AS employ," +
            " SUM(CASE WHEN Status = 0 THEN 1 ELSE 0 END) AS unemploy " +
            "FROM tracking;")
    statistic countEmploy();

    //返回所有专业的数组数据（管理员端）
    @Select("select * from major")
    List<major> allMajorList();

    //统计各专业就业人数（管理员端）
    @Select("select count(DISTINCT user.Name) as employedCount from major "+
            "join user ON major.MajorID = user.MajorID "+
            "join tracking  ON user.Name = tracking.StudentID "+
            "where tracking.Status = 1 "+
            "group by major.MajorID, major.MajorName "+
            "order by major.MajorID")
    List<Integer> countInMajor();

    //历年数据对比（管理员端）
    @Select("select * from previous")
    List<previous> previousData();

    //删除用户就业信息（管理员端）
    @Delete("delete from tracking where StudentID=#{id}")
    void deleteTrack(String id);

    //修改用户就业信息（管理员端）
    @Update("update tracking set Status=#{Status},Employer=#{Employer},Position=#{Position},UnemployReason=#{UnemployReason},PhoneNumber=#{PhoneNumber} where StudentID=#{StudentID}")
    void editTrack(tracking t);

    //姓名查找用户就业信息
    @Select("select * from tracking where Name like concat('%', #{name}, '%')")
    IPage<tracking> findByName(Page<tracking> trackingPage, String name);
}